package com.yuntsg.subject_database.auxiliary;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.json.JSONUtil;
import com.yuntsg.subject_database.util.CharUtil;
import com.yuntsg.subject_database.util.OftenUtils;
import lombok.Data;

import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

/**
 * 合并中文的期刊表，三个核心（北大、统计源、CSCD），四个数据库（知网、万方、维普、中华），合并到一起。方法是以ISSN号作为唯一值，先三个核心合并到一起，通过人工复核，发现全部正确，再把四个数据库合并进入，人工复核，发现也完全正确，程序就结束了。
 * 贾新志 2022.03.04
 */

public class MergerOfChineseCoreJournals {
    public static void main(String[] args) throws SQLException {

        String baoJourBeida = "RelationalTable.bao_jour_beida";
        String baoJourCscd = "RelationalTable.bao_jour_cscd";
        String baoJourSt = "RelationalTable.bao_jour_st";
        String matchJourWp = "RelationalTable.match_jour_wp";
        String matchJourWf = "RelationalTable.match_jour_wf";
        String matchJourCnki = "RelationalTable.match_jour_cnki";
        String matchJourYiigle = "RelationalTable.match_jour_yiigle";
        String cnCoreJour = "RelationalTable.cn_core_cscd_beida_tongji_table";

        String sciJourTable = "RelationalTable.jcr_jour_detail_2021_0720";
        String ncbiJourTable = "RelationalTable.jour_pubmed_zhang_2021_0720";
        String citeJourTable = "RelationalTable.cite_score_2021_0720";
        String zkyJourTable = "RelationalTable.zky_final_zfl_2021_0720";

//        Db.use().execute("truncate table " + cnCoreJour);

        Map<String, List<JourInfo>> coreJourMap = new HashMap<>();
        //"NCBI"、"SCIE"、"CNKI"、"wanFan"、"weiPu"、"CITE"、"LETPUB"、"ZKYJ"、"JIAJ"、"ZHUA"、"Beida"、"Cscd"、"TongJi"
        getCoreJourMap(baoJourBeida, "id", "name", "issn", coreJourMap, "Beida");
        getCoreJourMap(baoJourCscd, "id", "name", "issn", coreJourMap, "Cscd");
        getCoreJourMap(baoJourSt, "id", "name", "issn", coreJourMap, "TongJi");

        Map<String, Map<Integer, JourInfo>> dataAndIdAndJourInfoMap = new ConcurrentHashMap<>();//结构：数据库名称-表自增主键-期刊信息

        Map<String, Map<String, List<JourInfo>>> dataJourMap = new HashMap<>();
        getDataJourMap("id", "name", "", "", "issn", "cn", matchJourYiigle, "ZHUA", dataJourMap, "", "", "", "", dataAndIdAndJourInfoMap);
        getDataJourMap("id", "jour_name", "", "", "jour_issn", "jour_issn_cn", matchJourWp, "weiPu", dataJourMap, "", "", "", "", dataAndIdAndJourInfoMap);
        getDataJourMap("id", "jour_name", "jour_other_name", "", "jour_issn", "jour_issn_cn", matchJourWf, "wanFan", dataJourMap, "jour_old_name", "", "", "", dataAndIdAndJourInfoMap);
        getDataJourMap("id", "jour_cn_name", "jour_en_name", "", "jour_issn", "jour_cn", matchJourCnki, "CNKI", dataJourMap, "jour_old_name", "", "", "", dataAndIdAndJourInfoMap);
        getDataJourMap("id", "journal_name", "", "", "issn", "", sciJourTable, "SCIE", dataJourMap, "abbr_journal", "iso_abbreviation", "eissn", "", dataAndIdAndJourInfoMap);
        getDataJourMap("id", "NC_biao_ti", "NC_Title_s", "NC_Other_Title_s", "NC_PISSN", "", ncbiJourTable, "NCBI", dataJourMap, "NC_NLM_Title_Abbreviation", "NC_ISO_Abbreviation", "NC_EISSN", "NC_LISSN", dataAndIdAndJourInfoMap);
        getDataJourMap("id", "title", "old_title", "", "issn", "", citeJourTable, "CITE", dataJourMap, "", "", "eissn", "", dataAndIdAndJourInfoMap);
        getDataJourMap("id", "full_title", "", "", "issn", "", zkyJourTable, "ZKYJ", dataJourMap, "abbr_title", "", "", "", dataAndIdAndJourInfoMap);
        cnJourInsertTable(coreJourMap, cnCoreJour, dataJourMap);


// main
    }

    public static void jcrAndNlm(Map<String, Map<Integer, JourInfo>> dataAndIdAndJourInfoMap) {
        Map<Integer, JourInfo> scie = dataAndIdAndJourInfoMap.get("SCIE");
        Map<Integer, JourInfo> ncbi = dataAndIdAndJourInfoMap.get("NCBI");


        for (Map.Entry<Integer, JourInfo> entry : scie.entrySet()) {
            Integer scieId = entry.getKey();

        }

    }

    // 将期刊名称加入期刊集合。
    public static void addSet(Set<String> set, String str) {
        if (str.length() > 1) {
            set.add(CharUtil.reCnTa(str));
        }
    }

    // 将原来三个（北大、统计源、CSCD的期刊集合）与知网、万方、维普、中华的期刊集合再次合并，形成总的中文核心期刊集合。
    public static void addCnCoreList(Set<String> cnCoreList, Set<String> list) {
        for (String str : list) {
            if (str.length() > 1) {
                cnCoreList.add(CharUtil.reCnTa(str));
            }
        }
    }

    public static Set<String> getJourIssnList(JourInfo jourInfo) {
        Set<String> set = new HashSet<>();
        addSet(set, jourInfo.getPIssn());
        addSet(set, jourInfo.getEIssn());
        addSet(set, jourInfo.getLIssn());
        addSet(set, jourInfo.getCnIssn());
        return set;
    }

    public static Set<String> getJourNameList(JourInfo jourInfo) {
        Set<String> set = new HashSet<>();
        addSet(set, jourInfo.getAlias());
        addSet(set, jourInfo.getCnName());
        addSet(set, jourInfo.getEnName());
        addSet(set, jourInfo.getFull());
        addSet(set, jourInfo.getAbb());
        addSet(set, jourInfo.getIso());
        Set<String> oldSet = jourInfo.getOld();
        for (String old : oldSet) {
            addSet(set, old);
        }
        return set;
    }

    // 同一个数据库名称，同一个ISSN号，可能有多组期刊信息，循环该多组期刊信息，与中文期刊集合相比对，相似度得分最高的一组的得分、期刊集合、表的自增主键返回。
    public static JournalIdAndScore getDataList(List<JourInfo> jourInfoList, Set<String> coreList) {
        int cnkiId = 0;
        double cnkiScore = 0.0;
        Set<String> cnkiList = new HashSet<>();
        for (JourInfo jourInfo : jourInfoList) {
            Set<String> dataList = getJourNameList(jourInfo);
            for (String core : coreList) {
                for (String str : dataList) {
                    double similarityRatio = OftenUtils.getSimilarityRatio(core, str);
                    if (similarityRatio > cnkiScore) {
                        cnkiScore = similarityRatio;
                        cnkiList = dataList;
                        cnkiId = jourInfo.getAutoId();
                    }
                }
            }
        }
        JournalIdAndScore journalIdAndScore = new JournalIdAndScore();
        journalIdAndScore.setId(cnkiId);
        journalIdAndScore.setScore(cnkiScore);
        journalIdAndScore.setList(cnkiList);
        return journalIdAndScore;
    }

    // 经插表后测试，各个是正确的，因此，以后可以直接使用了。
    public static void cnJourInsertTable(Map<String, List<JourInfo>> coreJourMap, String cnCoreJour, Map<String, Map<String, List<JourInfo>>> dataJourMap) {
        for (Map.Entry<String, List<JourInfo>> entry : coreJourMap.entrySet()) {
            List<JourInfo> value = entry.getValue();
            String issn = entry.getKey();
            String BeidaName = "";
            String BeidaNameOther = "";
            String CscdName = "";
            String TongJiName = "";
            int BeidaId = 0;
            int CscdId = 0;
            int TongJiId = 0;
            double BeidaAndCscd = 0.0;
            double TongJiAndCscd = 0.0;
            double TongJiAndBeida = 0.0;
            Set<String> coreList = new HashSet<>();
            for (JourInfo jourInfo : value) {
                if (jourInfo.getData().equals("Beida")) {
                    BeidaId = jourInfo.getAutoId();
                    BeidaName = jourInfo.getCnName();
                    if (jourInfo.getAlias().length() > 0) {
                        BeidaNameOther = jourInfo.getAlias();
                    }
                }
                if (jourInfo.getData().equals("Cscd")) {
                    CscdId = jourInfo.getAutoId();
                    CscdName = jourInfo.getCnName();
                }
                if (jourInfo.getData().equals("TongJi")) {
                    TongJiId = jourInfo.getAutoId();
                    TongJiName = jourInfo.getCnName();
                }
                addSet(coreList, jourInfo.getCnName());
                addSet(coreList, jourInfo.getAlias());
            }
            // 获得北大与CSCD、北大与统计源、统计源与CSCD之间的得分。
            if (BeidaName.length() > 0 && CscdName.length() > 0) {
                BeidaAndCscd = OftenUtils.getSimilarityRatio(CharUtil.reCnTa(BeidaName), CharUtil.reCnTa(CscdName));
            }
            if (BeidaNameOther.length() > 0 && CscdName.length() > 0) {
                double similarityRatio = OftenUtils.getSimilarityRatio(CharUtil.reCnTa(BeidaNameOther), CharUtil.reCnTa(CscdName));
                if (similarityRatio > BeidaAndCscd) {
                    BeidaAndCscd = similarityRatio;
                }
            }
            if (TongJiName.length() > 0 && CscdName.length() > 0) {
                TongJiAndCscd = OftenUtils.getSimilarityRatio(CharUtil.reCnTa(TongJiName), CharUtil.reCnTa(CscdName));
            }
            if (TongJiName.length() > 0 && BeidaName.length() > 0) {
                TongJiAndBeida = OftenUtils.getSimilarityRatio(CharUtil.reCnTa(TongJiName), CharUtil.reCnTa(BeidaName));
            }
            if (BeidaNameOther.length() > 0 && TongJiName.length() > 0) {
                double similarityRatio = OftenUtils.getSimilarityRatio(CharUtil.reCnTa(BeidaNameOther), CharUtil.reCnTa(TongJiName));
                if (similarityRatio > TongJiAndBeida) {
                    TongJiAndBeida = similarityRatio;
                }
            }

            Set<String> cnkiList = new HashSet<>();
            Set<String> zHuaList = new HashSet<>();
            Set<String> wanFanList = new HashSet<>();
            Set<String> weiPuList = new HashSet<>();
            int cnkiId = 0;
            int zHuaId = 0;
            int wanFanId = 0;
            int weiPuId = 0;
            double cnkiScore = 0.0;
            double wanFanScore = 0.0;
            double weiPuScore = 0.0;
            double zHuaScore = 0.0;
            // 知网、万方、维普、中华与北大、统计源、CSCD相比对，获得知网、万方、维普、中华的期刊集合，以及各个的期刊id号和得分。
            if (dataJourMap.containsKey(issn)) {
                Map<String, List<JourInfo>> dataNameAndJourInfoListMap = dataJourMap.get(issn);
                for (Map.Entry<String, List<JourInfo>> entity : dataNameAndJourInfoListMap.entrySet()) {
                    String dataName = entity.getKey();
                    List<JourInfo> jourInfoList = entity.getValue();
                    if (dataName.equals("CNKI")) {
                        JournalIdAndScore data = getDataList(jourInfoList, coreList);
                        cnkiList = data.getList();
                        cnkiId = data.getId();
                        cnkiScore = data.getScore();
                    }
                    if (dataName.equals("wanFan")) {
                        JournalIdAndScore data = getDataList(jourInfoList, coreList);
                        wanFanList = data.getList();
                        wanFanId = data.getId();
                        wanFanScore = data.getScore();
                    }
                    if (dataName.equals("weiPu")) {
                        JournalIdAndScore data = getDataList(jourInfoList, coreList);
                        weiPuList = data.getList();
                        weiPuId = data.getId();
                        weiPuScore = data.getScore();
                    }
                    if (dataName.equals("ZHUA")) {
                        JournalIdAndScore data = getDataList(jourInfoList, coreList);
                        zHuaList = data.getList();
                        zHuaId = data.getId();
                        zHuaScore = data.getScore();
                    }
                }
            }
            Set<String> cnCoreList = new HashSet<>();
            addCnCoreList(cnCoreList, coreList);
            addCnCoreList(cnCoreList, cnkiList);
            addCnCoreList(cnCoreList, zHuaList);
            addCnCoreList(cnCoreList, wanFanList);
            addCnCoreList(cnCoreList, weiPuList);

            int jcrId = 0;
            int ncbiId = 0;
            int citeId = 0;
            int zkyId = 0;
            Set<String> jcrList = new HashSet<>();
            Set<String> ncbiList = new HashSet<>();
            Set<String> citeList = new HashSet<>();
            Set<String> zkyList = new HashSet<>();
            double jcrScore = 0.0;
            double ncbiScore = 0.0;
            double citeScore = 0.0;
            double zkyScore = 0.0;
            // SCIE、NCBI、CITE、ZKYJ与所有中文期刊名称的集合的最高得分，以及最高得分下的期刊id号、期刊集合。
            if (dataJourMap.containsKey(issn)) {
                Map<String, List<JourInfo>> dataNameAndJourInfoListMap = dataJourMap.get(issn);
                for (Map.Entry<String, List<JourInfo>> entity : dataNameAndJourInfoListMap.entrySet()) {
                    String dataName = entity.getKey();
                    List<JourInfo> jourInfoList = entity.getValue();
                    if (dataName.equals("SCIE")) {
                        JournalIdAndScore data = getDataList(jourInfoList, cnCoreList);
                        jcrList = data.getList();
                        jcrId = data.getId();
                        jcrScore = data.getScore();
                    }
                    if (dataName.equals("NCBI")) {
                        JournalIdAndScore data = getDataList(jourInfoList, cnCoreList);
                        ncbiList = data.getList();
                        ncbiId = data.getId();
                        ncbiScore = data.getScore();
                    }
                    if (dataName.equals("CITE")) {
                        JournalIdAndScore data = getDataList(jourInfoList, cnCoreList);
                        citeList = data.getList();
                        citeId = data.getId();
                        citeScore = data.getScore();
                    }
                    if (dataName.equals("ZKYJ")) {
                        JournalIdAndScore data = getDataList(jourInfoList, cnCoreList);
                        zkyList = data.getList();
                        zkyId = data.getId();
                        zkyScore = data.getScore();
                    }
                }
            }
            try {
                Db.use().insert(Entity.create(cnCoreJour)
                        .set("issn", entry.getKey())
                        .set("beida_name", BeidaName)
                        .set("cscd_name", CscdName)
                        .set("tongji_name", TongJiName)
                        .set("beida_id", BeidaId)
                        .set("cscd_id", CscdId)
                        .set("tongji_id", TongJiId)
                        .set("beida_other", BeidaNameOther)
                        .set("beida_cscd_score", OftenUtils.takeFourDigits(BeidaAndCscd))
                        .set("tongji_cscd_score", OftenUtils.takeFourDigits(TongJiAndCscd))
                        .set("tongji_beida_score", OftenUtils.takeFourDigits(TongJiAndBeida))
                        .set("cnki_score", OftenUtils.takeFourDigits(cnkiScore))
                        .set("zhua_score", OftenUtils.takeFourDigits(zHuaScore))
                        .set("wanfan_score", OftenUtils.takeFourDigits(wanFanScore))
                        .set("weipu_score", OftenUtils.takeFourDigits(weiPuScore))
                        .set("cnki_id", cnkiId)
                        .set("zhua_id", zHuaId)
                        .set("wanfan_id", wanFanId)
                        .set("weipu_id", weiPuId)
                        .set("core_name", JSONUtil.toJsonStr(new HashSet<>(coreList)))
                        .set("cnki_name", JSONUtil.toJsonStr(cnkiList))
                        .set("zhua_name", JSONUtil.toJsonStr(zHuaList))
                        .set("wanfan_name", JSONUtil.toJsonStr(wanFanList))
                        .set("weipu_name", JSONUtil.toJsonStr(weiPuList))
                        .set("jcr_score", OftenUtils.takeFourDigits(jcrScore))
                        .set("ncbi_score", OftenUtils.takeFourDigits(ncbiScore))
                        .set("zky_score", OftenUtils.takeFourDigits(zkyScore))
                        .set("cite_score", OftenUtils.takeFourDigits(citeScore))
                        .set("jcr_id", jcrId)
                        .set("ncbi_id", ncbiId)
                        .set("zky_id", zkyId)
                        .set("cite_id", citeId)
                        .set("jcr_name", JSONUtil.toJsonStr(jcrList))
                        .set("ncbi_name", JSONUtil.toJsonStr(ncbiList))
                        .set("zky_name", JSONUtil.toJsonStr(zkyList))
                        .set("cite_name", JSONUtil.toJsonStr(citeList))
                        .set("cn_core_name", JSONUtil.toJsonStr(new HashSet<>(cnCoreList)))
                );
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public static void getDataJourMap(String autoId, String full, String alias, String old, String pIssn, String cn, String tableName, String dataName, Map<String, Map<String, List<JourInfo>>> dataJourMap, String abb, String iso, String eIssn, String lIssn, Map<String, Map<Integer, JourInfo>> dataAndIdAndJourInfoMap) throws SQLException {
        String select = ("`" + autoId + "`,`" + full + "`,`" + alias + "`,`" + old + "`,`" + pIssn + "`,`" + abb + "`,`" + iso + "`,`" + eIssn + "`,`" + lIssn + "`,`" + cn + "`").replace("``,", "").replace(",``", "");
        List<Entity> tableData = Db.use().query("select " + select + " from " + tableName);
        for (Entity entry : tableData) {
            JourInfo jourInfo = new JourInfo();
            jourInfo.setData(dataName);

            Integer id = OftenUtils.getIntField(entry, autoId);
            jourInfo.setAutoId(id);

            String aliasS = OftenUtils.getStrField(entry, alias);
            jourInfo.setAlias(aliasS);

            String oldS = OftenUtils.getStrField(entry, old);
            if (oldS.length() > 1) {
                String[] split = oldS.split("; ");
                Set<String> oldSet = new HashSet<>(Arrays.asList(split));
                jourInfo.setOld(oldSet);
            }

            String pIssnS = OftenUtils.getStrField(entry, pIssn);
            jourInfo.setPIssn(pIssnS);

            String eIssnS = OftenUtils.getStrField(entry, eIssn);
            jourInfo.setEIssn(eIssnS);

            String lIssnS = OftenUtils.getStrField(entry, lIssn);
            jourInfo.setLIssn(lIssnS);

            String cnS = OftenUtils.getStrField(entry, cn);
            jourInfo.setCnIssn(cnS);

            String fullS = OftenUtils.getStrField(entry, full);
            jourInfo.setFull(fullS);

            String abbS = OftenUtils.getStrField(entry, abb);
            jourInfo.setAbb(abbS);

            String isoS = OftenUtils.getStrField(entry, iso);
            jourInfo.setIso(isoS);

            dataAndIdAndJourInfoMap.compute(dataName, (k, v) -> {
                if (v == null) {
                    v = new ConcurrentHashMap<>();
                }
                v.put(id, jourInfo);
                return v;
            });

            putDataJourMap(dataJourMap, pIssnS, dataName, jourInfo);
            putDataJourMap(dataJourMap, eIssnS, dataName, jourInfo);
            putDataJourMap(dataJourMap, lIssnS, dataName, jourInfo);

        }
    }

    public static void putDataJourMap(Map<String, Map<String, List<JourInfo>>> dataJourMap, String issn, String type, JourInfo jourInfo) {
        if (issn.length() > 8 && type.length() > 1) {
            dataJourMap.compute(issn, (k, v) -> {
                if (v == null) {
                    v = new ConcurrentHashMap<>();
                }
                v.compute(type, (k1, v1) -> {
                    if (v1 == null) {
                        v1 = Collections.synchronizedList(new ArrayList<>());
                    }
                    v1.add(jourInfo);
                    return v1;
                });
                return v;
            });
        }
    }

    public static void getCoreJourMap(String tableName, String autoId, String name, String issn, Map<String, List<JourInfo>> coreJourMap, String dataName) throws SQLException {
        List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + name + "`,`" + issn + "` from " + tableName);
        for (Entity entry : tableData) {
            Integer id = OftenUtils.getIntField(entry, autoId);
            String jourName = OftenUtils.getStrField(entry, name).trim();
            String jour = jourName;
            String jourIssn = OftenUtils.getStrField(entry, issn).toLowerCase().replace(" ", "");
            JourInfo jourInfo = new JourInfo();
            if (jourName.indexOf("（改名为：") > 2 && jourName.endsWith("）")) {
                jourName = jour.substring(0, jourName.indexOf("（改名为："));
                String alias = jour.substring(jour.indexOf("（改名为：") + "（改名为：".length(), jour.length() - "）".length());
                jourInfo.setAlias(alias);
            }
            if (jourName.indexOf("（原分类：") > 2 && jourName.endsWith("）")) {
                jourName = jour.substring(0, jourName.indexOf("（原分类："));
            }
            jourInfo.setAutoId(id);
            jourInfo.setCnName(jourName);
            jourInfo.setPIssn(jourIssn);
            jourInfo.setData(dataName);
            coreJourMap.compute(jourIssn, (k, v) -> {
                if (v == null) {
                    v = Collections.synchronizedList(new ArrayList<>());
                }
                v.add(jourInfo);
                return v;
            });
            if (jourIssn.length() < 9) {
                System.out.println("-------jourIssn.length(): " + jourInfo);
            }
        }
    }

    @Data
    //最终结果的实体类
    public static class JournalIdAndScore {
        private Integer id = 0;
        private Double score = 0.0;
        private Set<String> list = new HashSet<>();
    }

    @Data
    public static class JourInfo {
        private int autoId = 0;
        private String full = "";
        private String abb = "";
        private String iso = "";
        private String alias = "";
        private String title = "";
        private String cnName = "";
        private String enName = "";
        private String jourId = "";
        private Set<String> old = new HashSet<>();
        private String pIssn = "";
        private String eIssn = "";
        private String lIssn = "";
        private String cnIssn = "";
        private String url = "";
        private String data = "";
    }


// end
}
